﻿/*                        Used for Stored Procedure                    */

#region NameSpace
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using ebird.StoredProcExecution;
#endregion

namespace ebird.eBirdGenerics
{
   public class Generics
   {
       #region Fields
       SqlConnection Connection;
       string connectionString = string.Empty;
       DataSet dataSet;
       SqlDataAdapter sqlDataAdapter;       
       #endregion

       #region Constructor
       /// <summary>
       /// To Connect Database
       /// </summary>
       /// <param name="connectionString"></param>
       public Generics(string connectionString)
       {
          // connectionString = ConfigurationManager.ConnectionStrings["connectUserManager"].ToString();
           Connection = new SqlConnection(connectionString);

       }
       #endregion

       #region Methods
       /// <summary>
       /// ToExecute StoredProcedure (Insert/Delete/Update)
       /// Returns Number of rows Affected
       /// </summary>
       /// <param name="spCollection"></param>
       /// <returns></returns>
       public bool ExecuteNonQuery(StoredProcedureCollection spCollection)
       {
           int recCount = 0;
           bool status;
           try
           {
               foreach (StoredProcedure spData in spCollection)
               {
                   SqlCommand cmd = new SqlCommand();
                   int i = 0;
                   if (Connection.State != ConnectionState.Open)
                       Connection.Open();
                   cmd.Connection = Connection;
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.CommandText = spData.ProcName;
                   IEnumerator myEnumerator = spData.GetParams().GetEnumerator();
                   while (myEnumerator.MoveNext())
                   {
                       ParamData Data = (ParamData)myEnumerator.Current;
                       cmd.Parameters.Add(Data.pName, Data.pDataType);
                       cmd.Parameters[i].Value = Data.pValue;
                       i = i + 1;
                   }
                   recCount= cmd.ExecuteNonQuery();
                   Connection.Close();
               }
               if (recCount > 0)
               {
                   status= true;
               }
               else
               {
                   status= false;
               }
               return status;
           }
           catch (Exception exc)
           {
               return false;
           }
       }

       /// <summary>
       /// returns dataset
       /// </summary>
       /// <param name="spCollection"></param>
       /// <returns></returns>
       public DataSet ExecuteQuery(StoredProcedureCollection spCollection)
       {
           try
           {
               foreach (StoredProcedure spData in spCollection)
               {
                   SqlCommand cmd = new SqlCommand();
                   dataSet = new DataSet();
                   int i = 0;
                   if (Connection.State != ConnectionState.Open)
                       Connection.Open();
                   cmd.Connection = Connection;
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.CommandText = spData.ProcName;
                   IEnumerator myEnumerator = spData.GetParams().GetEnumerator();
                   while (myEnumerator.MoveNext())
                   {
                       ParamData Data = (ParamData)myEnumerator.Current;
                       cmd.Parameters.Add(Data.pName, Data.pDataType);
                       cmd.Parameters[i].Value = Data.pValue;
                       i = i + 1;
                   }
                   sqlDataAdapter = new SqlDataAdapter(cmd);
                   sqlDataAdapter.Fill(dataSet);
                   Connection.Close();
                 //  cmd.ExecuteNonQuery();
               }
               return dataSet;
           }
           catch (Exception exc)
           {
               return dataSet;
           }
       }

       /// <summary>
       /// Returns ist Column of ist row
       /// </summary>
       /// <param name="spCollection"></param>
       /// <returns></returns>
       public object ExecuteScalar(StoredProcedureCollection spCollection)
       {
           object value = null;

           try
           {
               foreach (StoredProcedure spData in spCollection)
               {
                   SqlCommand cmd = new SqlCommand();
                   int i = 0;
                   if (Connection.State != ConnectionState.Open)
                       Connection.Open();
                   cmd.Connection = Connection;
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.CommandText = spData.ProcName;
                   IEnumerator myEnumerator = spData.GetParams().GetEnumerator();
                   while (myEnumerator.MoveNext())
                   {
                       ParamData Data = (ParamData)myEnumerator.Current;
                       cmd.Parameters.Add(Data.pName, Data.pDataType);
                       cmd.Parameters[i].Value = Data.pValue;
                       i = i + 1;
                   }
                  value= cmd.ExecuteScalar();
               }
               return value;
           }
           catch (Exception exc)
           {
               return value;
           }
       }
       #endregion

   }
}

//This class is used to add Parameters in StoredProcedure
namespace ebird.StoredProcExecution
{
    #region Structure
    public struct ParamData
    {
        #region Fields
        public string pName, pValue;
        public SqlDbType pDataType;
        #endregion

        #region Str
        /// <summary>
        /// To set parameters
        /// </summary>
        /// <param name="pName"></param>
        /// <param name="pDataType"></param>
        /// <param name="pValue"></param>
        public ParamData(string pName, SqlDbType pDataType, string pValue)
        {
            this.pName = pName;
            this.pDataType = pDataType;
            this.pValue = pValue;
        }
        #endregion
    }
    #endregion 

    /// <summary>
    /// Summary description for StoredProcedure
    /// </summary>
    public class StoredProcedure
    {

        #region Constructor
        public StoredProcedure()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        #endregion

        #region Fields
        private string sProcName;
        private ArrayList sParams = new ArrayList();
        #endregion

        #region Methods
        /// <summary>
        /// To set Parameter
        /// </summary>
        /// <param name="pName"></param>
        /// <param name="pDataType"></param>
        /// <param name="pValue"></param>
        public void SetParam(string pName, SqlDbType pDataType, string pValue)
        {
            ParamData pData = new ParamData(pName, pDataType, pValue);
            sParams.Add(pData);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public ArrayList GetParams()
        {

            if (!(sParams == null))
            {
                return sParams;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Procedurename
        /// </summary>
        public string ProcName
        {
            get
            {
                return sProcName;
            }
            set
            {
                sProcName = value;
            }
        }

        #endregion
    }

    public class StoredProcedureCollection : System.Collections.CollectionBase
    {
        #region Methods
        /// <summary>
        /// To add Parameter value
        /// </summary>
        /// <param name="value"></param>
        public void add(StoredProcedure value)
        {
            List.Add(value);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="index"></param>
        public void Remove(int index)
        {
            if (index > Count - 1 || index < 0)
            {
                //ignore
                Console.WriteLine("No data to remove");
            }
            else
            {
                List.RemoveAt(index);
            }
        }

        ///
        public StoredProcedure Item(int Index)
        {
            return (StoredProcedure)List[Index];
        }
        #endregion
    }
}
